/*********** NOTES HEADER *********** 

DESCRIPTION: This file puts together the Area Resources File for inclusion with the Medicare claims dataset

Data used:
 (1) SSA_FIPS_crosswalk.xlsx
 (2) zip_county_xwalk2010.dta
 (3) arf2009
 (4) arf_2015
  
 Data created:
 (1) CountyChars2005_2015.dta (Includes ZIPs and counties)
 (2) CountyChars2005_2015_noZIP.dta (Excludes ZIP codes for single record per county)
 
************************************/ 

set more off
capture log close
clear all

global origData "N:\MedicareClaims-P045601-BE"
global dataIn "N:\MedicareClaims-P045601-BE\Work\hosp_retro\health_out\Data-In\"
global dataOut "N:\MedicareClaims-P045601-BE\Work\hosp_retro\health_out\Data-Out\PropScore"
global dpath "N:\MedicareClaims-P045601-BE\Work\ay_data"
global skapath "N:\MedicareClaims-P045601-BE\Work\ska"

adopath +  N:\SIL-Common\estout
adopath +  N:\SIL-Common\outreg2

adopath +  N:\SIL-Common\estout
adopath +  N:\SIL-Common\outreg2


*import excel "Specialties_description.xlsx", sheet(sheet2)

*Fips-SSA mapping 
 import excel "N:\SIL-Common\Hospital Files\Area Resource File\ARF\SSA_FIPS_crosswalk.xlsx", sheet("crosswalk") firstrow
 tempfile ssafipsxw
 keep FIPS SSA
 rename FIPS fips_sc
 rename SSA ssa_sc
 duplicates drop
 sort fips_sc
 drop if ssa_sc=="05210" & 	fips_sc==	"06037"

 save `ssafipsxw', replace
 clear


*ARF 2009
 use f13226* f00011 f00012 f1167300 f1387600 f0973800 f0002003 f1255903 f0884500 f1139107 f1139105 f1139100 f1139195  f1139207 f1139205 f1139200 f1139295  f1139307 f1139305 f1139300 f1139395  f1139407 f1139405 f1139400 f1139495 f1138607 f1138605 f1138600 f1138695 f1138707 f1138705 f1138700 f1138795 f1138807 f1138805 f1138800 f1138895  f1138907 f1138905 f1138900 f1138995 f1139007 f1139005 f1139000 f1139095 using "N:\SIL-Common\Hospital Files\Area Resource File\arf2009"


foreach var of varlist f13226* {
local i = substr("`var'",7,2)
local j = "20" + "`i'"  
di "`i'"
di "`j'"
rename `var' hhmedinc_`j'
}
rename f00011 fips_state 
rename f00012 fips_cnty
rename f0002003 urban_rural
rename f1255903 urban_influence
rename f1167300 hs_ownocc2000 
rename f0973800 hs_medval2000  
rename f0884500 hs_medrnt2000 
rename f1387600 pop_density2000 

*hospital nearby utilization
foreach var of varlist f1139*  {
local k = substr("`var'",6,1)
local i = substr("`var'",7,2)
local j = "20" + "`i'"  
di "`i'"
di "`j'"
rename `var' hospmile`k'_`j'
}

*hospital nearby bedsize
foreach var of varlist f1138*  {
local k = substr("`var'",6,1)
local i = substr("`var'",7,2)
local j = "20" + "`i'"  
di "`i'"
di "`j'"
rename `var' hospbedsz`k'_`j'
}


reshape long hhmedinc_ hospbedsz6_ hospbedsz7_ hospbedsz8_ hospbedsz9_ hospmile0_ hospmile1_ hospmile2_ hospmile3_ hospmile4_, i(fips* hs*) j(year)
keep if inlist(year, 2005, 2006, 2007)

tempfile inc0008
save `inc0008', replace
clear

*ARF 2015
 use f13226* f1461309  f1461306  f1461409  f1461406 f1167310  f1167300 f00011 f00012 f0002013 f1255913 f1139112 f1139110 f1139105 f1139212 f1139210 f1139205 f1139312 f1139310 f1139305 f1139412 f1139410 f1139405 f1138612 f1138610 f1138605 f1138712 f1138710 f1138705 f1138812 f1138810 f1138805 f1138912 f1138910 f1138905  f1139012 f1139010 f1139005  f1387600  f1387610 using "N:\SIL-Common\Hospital Files\Area Resource File\ARF\arf_2015.dta"

foreach var of varlist f13226*  {
local i = substr("`var'",7,2)
local j = "20" + "`i'"  
di "`i'"
di "`j'"
rename `var' hhmedinc_`j'
}

*hospital nearby utilization
foreach var of varlist f1139*  {
local k = substr("`var'",6,1)
local i = substr("`var'",7,2)
local j = "20" + "`i'"  
di "`i'"
di "`j'"
rename `var' hospmile`k'_`j'
}

*hospital nearby bedsize
foreach var of varlist f1138*  {
local k = substr("`var'",6,1)
local i = substr("`var'",7,2)
local j = "20" + "`i'"  
di "`i'"
di "`j'"
rename `var' hospbedsz`k'_`j'
}

rename f00011 fips_state 
rename f00012 fips_cnty
rename f0002013 urban_rural
rename f1255913 urban_influence
rename f1387600 pop_density2000
rename f1387610 pop_density2010
rename f1461309 hs_medval2010 
rename f1461306 hs_medval2006 
rename f1461409 hs_medrnt2010 
rename f1461406 hs_medrnt2006 
rename f1167310 hs_ownocc2010
rename f1167300 hs_ownocc2006


reshape long hhmedinc_ hospbedsz6_ hospbedsz7_ hospbedsz8_ hospbedsz9_ hospmile0_ hospmile1_ hospmile2_ hospmile3_ hospmile4_ , i(fips* hs*) j(year)

drop if year<=2007

*Together
append using `inc0008'

gen fips_sc = fips_state+fips_c
sort fips_sc year

foreach var of varlist hhmedinc_ hospbedsz6_ hospbedsz7_ hospbedsz8_ hospbedsz9_ hospmile0_ hospmile1_ hospmile2_ hospmile3_ hospmile4_ {
local k = subinstr("`var'","_","",.)
rename `var' `k'
}

foreach var in "hs_ownocc" "hs_medval" "hs_medrnt" "pop_density" {
gen `var'=`var'2010 
 by fips_sc: replace `var' = `var'[_N] if `var'==.
 replace `var'=`var'2000 if year<=2005 
}

foreach var of varlist *2006  {
 by fips_sc: replace `var' =`var'[_N] if `var'==.
}

foreach var in "hs_ownocc" "hs_medval" "hs_medrnt" {
 replace `var'=`var'2006 if year<=2008 & year>2005
}


drop hs_ownocc2*  hs_ownocc2* hs_medval2* hs_medrnt2* pop_density2*  

merge m:1 fips_sc using `ssafipsxw'
sort ssa_sc

drop if _merge~=3 
drop _merge

destring urban_rural urban_influence , replace
sort ssa_sc year

foreach var of varlist urban_rural urban_influence hospbedsz6 hospbedsz7 hospbedsz8 hospbedsz9 hospmile0 hospmile1 hospmile2 hospmile3 hospmile4 pop_density {
replace `var' = `var'[_n-1] if year!=2005 & `var'==.
replace `var' = `var'[_n+1] if year!=2013 & `var'==.
}

rename hospbedsz6 hospbedsz_lt49
rename hospbedsz7 hospbedsz_5099 
rename hospbedsz8 hospbedsz_100199 
rename hospbedsz9 hospbedsz_200299 
*Note that this one was miscoded because the original variable prefix, f1139, was similar to hospmile variables. This is now correct
rename hospmile0 hospbedsz_gt300

rename hospmile1 hosputil_lt40
rename hospmile2 hosputil_4059
rename hospmile3 hosputil_6079
rename hospmile4 hosputil_gt80

sort fips_sc year
tempfile cntyinc
save `cntyinc', replace

*In order to merge the county-year dataset to the zip-county dataset, I need the zip-county file to have years
keep year
duplicates drop
tempfile dyear
save `dyear', 
clear


*Zip to County crosswalk
tempfile zcxwalk
use "N:\SIL-Common\Hospital Files\Geography\zip_county_xwalk2010.dta" 
keep zcta5 state_numeric county_numeric county
rename zcta5 PHYS_ZIP

tostring county_numeric, replace
tostring state_numeric , replace
replace  county_numeric="00"+county_numeric if length(county_numeric )==1
replace  county_numeric="0"+county_numeric if length(county_numeric )==2
replace  state_numeric="0"+state_numeric if length(state_numeric)==1 
gen fips_sc=state_numeric+county_numeric
drop county_numeric state_numeric
cross using `dyear'

sort fips_sc year

merge m:1 fips_sc year using `cntyinc'
drop if _merge==1
drop _merge

sort PHYS_ZIP year
save "$dataOut\CountyChars2005_2015.dta", replace
 
 drop PHYS_ZIP 
 duplicates drop

save "$dataOut\CountyChars2005_2015_noZIP.dta", replace



 